﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spDBA_RebuildCentralServerList]
#-- Purpose:		Rebuilds msdb.dbo.sysmanagement_shared_registered_servers_internal
#--					and msdb.dbo.sysmanagement_shared_server_groups_internal
#--					based on the information stored in tblServers
#--	Last Update:	06/06/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spDBA_RebuildCentralServerList]
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @group2000P int, @group2000Q int, @group2000T int, @group2000Unk int,
		@groupP int, @groupQ int, @groupT int, @groupUnk int,
		@groupOther int

--- If a server is not disabled, but also is not active and has MSX jobs,
---	   automatically enable the server
UPDATE	dbo.tblServer
SET		srv_is_active = 1
WHERE	srv_is_active = 0
		AND srv_is_disabled = 0
		AND srv_has_master_jobs = 1

--- Remove the current records from sysmanagement
TRUNCATE TABLE [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
DELETE FROM msdb.dbo.sysmanagement_shared_server_groups_internal WHERE is_system_object <> 1
DBCC CHECKIDENT('msdb.dbo.sysmanagement_shared_server_groups_internal', RESEED, 5) ;

--- Create the Groups (Application then Server List)
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	VALUES ('Applications', '', 0, 1, 0)
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	SELECT		DISTINCT
				ISNULL(srv_description, 'Unknown') name,
				'' description,
				0 server_type,
				(
				SELECT		server_group_id
				FROM		msdb.dbo.sysmanagement_shared_server_groups_internal
				WHERE		name = 'Applications'
							AND parent_id = 1
				) parent_id,
				0 is_system_object
	FROM		dbo.tblServer s
	LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
				AND s.srv_version_minor_ro = sv.ver_minor_ro
	WHERE		s.srv_is_active = 1

--- Insert the Servers into the Groups (Application then Server List)
INSERT INTO [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
	(server_group_id, name, server_name, description, server_type)
	SELECT	(
			SELECT		t2.server_group_id
			FROM		msdb.dbo.sysmanagement_shared_server_groups_internal t1
			JOIN		msdb.dbo.sysmanagement_shared_server_groups_internal t2
							ON t1.server_group_id = t2.parent_id
			WHERE		t1.name = 'Applications'
						AND t1.parent_id = 1
						AND t2.name = tServ.srv_description
			) server_group_id, 
			srv_type_ro + ' - ' + srv_server_name name, 
			srv_server_name server_name, 
			ISNULL(srv_description, '') description,
			0 server_type
	FROM	(
			SELECT 		s.srv_type_ro,
						ISNULL(sv.ver_description, 'Unknown') SQLServer_description,
						s.srv_server_name,
						s.srv_description
			FROM		dbo.tblServer s
			LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
						AND s.srv_version_minor_ro = sv.ver_minor_ro
			WHERE		s.srv_is_active = 1
			) tServ


--- Create the Groups (Environment then SQL Version)
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	VALUES ('Environment', '', 0, 1, 0)
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	SELECT		DISTINCT
				srv_type_ro name,
				'' description,
				0 server_type,
				(
				SELECT		server_group_id
				FROM		msdb.dbo.sysmanagement_shared_server_groups_internal
				WHERE		name = 'Environment'
							AND parent_id = 1
				) parent_id,
				0 is_system_object
	FROM		dbo.tblServer s
	LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
				AND s.srv_version_minor_ro = sv.ver_minor_ro
	WHERE		s.srv_is_active = 1
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	SELECT 		DISTINCT
				ISNULL(sv.ver_description, 'Unknown') name,
				'' description,
				0 server_type,
				(
				SELECT		t2.server_group_id
				FROM		msdb.dbo.sysmanagement_shared_server_groups_internal t1
				JOIN		msdb.dbo.sysmanagement_shared_server_groups_internal t2
								ON t1.server_group_id = t2.parent_id
				WHERE		t1.name = 'Environment'
							AND t1.parent_id = 1
							AND t2.name = s.srv_type_ro
				) parent_id,
				0 is_system_object
	FROM		dbo.tblServer s
	LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
				AND s.srv_version_minor_ro = sv.ver_minor_ro
	WHERE		s.srv_is_active = 1

--- Insert the Servers into the Groups (Environment then SQL Version)
INSERT INTO [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
	(server_group_id, name, server_name, description, server_type)
	SELECT	(
			SELECT		t3.server_group_id
			FROM		msdb.dbo.sysmanagement_shared_server_groups_internal t1
			JOIN		msdb.dbo.sysmanagement_shared_server_groups_internal t2
							ON t1.server_group_id = t2.parent_id
			JOIN		msdb.dbo.sysmanagement_shared_server_groups_internal t3
							ON t2.server_group_id = t3.parent_id
			WHERE		t1.name = 'Environment'
						AND t1.parent_id = 1
						AND t2.name = tServ.srv_type_ro
						AND t3.name = tServ.SQLServer_description
			) server_group_id, 
			srv_server_name name, 
			srv_server_name server_name, 
			ISNULL(srv_description, '') description,
			0 server_type
	FROM	(
			SELECT 		s.srv_type_ro,
						ISNULL(sv.ver_description, 'Unknown') SQLServer_description,
						s.srv_server_name,
						s.srv_description
			FROM		dbo.tblServer s
			LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
						AND s.srv_version_minor_ro = sv.ver_minor_ro
			WHERE		s.srv_is_active = 1
			) tServ

--- Create the Groups (MSX Managed then SQL Version)
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	VALUES ('MSX Managed', '', 0, 1, 0)
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	SELECT		DISTINCT
				srv_type_ro name,
				'' description,
				0 server_type,
				(
				SELECT		server_group_id
				FROM		msdb.dbo.sysmanagement_shared_server_groups_internal
				WHERE		name = 'MSX Managed'
							AND parent_id = 1
				) parent_id,
				0 is_system_object
	FROM		dbo.tblServer s
	LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
				AND s.srv_version_minor_ro = sv.ver_minor_ro
	WHERE		s.srv_is_active = 1
				AND s.srv_has_master_jobs = 1
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	SELECT 		DISTINCT
				ISNULL(sv.ver_description, 'Unknown') name,
				'' description,
				0 server_type,
				(
				SELECT		t2.server_group_id
				FROM		msdb.dbo.sysmanagement_shared_server_groups_internal t1
				JOIN		msdb.dbo.sysmanagement_shared_server_groups_internal t2
								ON t1.server_group_id = t2.parent_id
				WHERE		t1.name = 'MSX Managed'
							AND t1.parent_id = 1
							AND t2.name = s.srv_type_ro
				) parent_id,
				0 is_system_object
	FROM		dbo.tblServer s
	LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
				AND s.srv_version_minor_ro = sv.ver_minor_ro
	WHERE		s.srv_is_active = 1
				AND s.srv_has_master_jobs = 1

--- Insert the Servers into the Groups (MSX Managed then SQL Version)
INSERT INTO [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
	(server_group_id, name, server_name, description, server_type)
	SELECT	(
			SELECT		t3.server_group_id
			FROM		msdb.dbo.sysmanagement_shared_server_groups_internal t1
			JOIN		msdb.dbo.sysmanagement_shared_server_groups_internal t2
							ON t1.server_group_id = t2.parent_id
			JOIN		msdb.dbo.sysmanagement_shared_server_groups_internal t3
							ON t2.server_group_id = t3.parent_id
			WHERE		t1.name = 'MSX Managed'
						AND t1.parent_id = 1
						AND t2.name = tServ.srv_type_ro
						AND t3.name = tServ.SQLServer_description
			) server_group_id, 
			srv_server_name name, 
			srv_server_name server_name, 
			ISNULL(srv_description, '') description,
			0 server_type
	FROM	(
			SELECT 		s.srv_type_ro,
						ISNULL(sv.ver_description, 'Unknown') SQLServer_description,
						s.srv_server_name,
						s.srv_description
			FROM		dbo.tblServer s
			LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
						AND s.srv_version_minor_ro = sv.ver_minor_ro
			WHERE		s.srv_is_active = 1
						AND s.srv_has_master_jobs = 1
			) tServ
			
--- Create the Groups (SQL Version then Environment)
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	VALUES ('SQL_Version', '', 0, 1, 0)
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	SELECT 		DISTINCT
				ISNULL(sv.ver_description, 'Unknown') name,
				'' description,
				0 server_type,
				(
				SELECT		server_group_id
				FROM		msdb.dbo.sysmanagement_shared_server_groups_internal
				WHERE		name = 'SQL_Version'
							AND parent_id = 1
				) parent_id,
				0 is_system_object
	FROM		dbo.tblServer s
	LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
				AND s.srv_version_minor_ro = sv.ver_minor_ro
	WHERE		s.srv_is_active = 1
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	SELECT		DISTINCT
				srv_type_ro name,
				'' description,
				0 server_type,
				(
				SELECT		t2.server_group_id
				FROM		msdb.dbo.sysmanagement_shared_server_groups_internal t1
				JOIN		msdb.dbo.sysmanagement_shared_server_groups_internal t2
								ON t1.server_group_id = t2.parent_id
				WHERE		t1.name = 'SQL_Version'
							AND t1.parent_id = 1
							AND t2.name = ISNULL(sv.ver_description, 'Unknown')
				) parent_id,
				0 is_system_object
	FROM		dbo.tblServer s
	LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
				AND s.srv_version_minor_ro = sv.ver_minor_ro
	WHERE		s.srv_is_active = 1

--- Insert the Servers into the Groups (SQL Version then Environment)
INSERT INTO [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
	(server_group_id, name, server_name, description, server_type)
	SELECT	(
			SELECT		t3.server_group_id
			FROM		msdb.dbo.sysmanagement_shared_server_groups_internal t1
			JOIN		msdb.dbo.sysmanagement_shared_server_groups_internal t2
							ON t1.server_group_id = t2.parent_id
			JOIN		msdb.dbo.sysmanagement_shared_server_groups_internal t3
							ON t2.server_group_id = t3.parent_id
			WHERE		t1.name = 'SQL_Version'
						AND t1.parent_id = 1
						AND t2.name = tServ.SQLServer_description
						AND t3.name = tServ.srv_type_ro
			) server_group_id, 
			srv_server_name name, 
			srv_server_name server_name, 
			ISNULL(srv_description, '') description,
			0 server_type
	FROM	(
			SELECT 		s.srv_type_ro,
						ISNULL(sv.ver_description, 'Unknown') SQLServer_description,
						s.srv_server_name,
						s.srv_description
			FROM		dbo.tblServer s
			LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
						AND s.srv_version_minor_ro = sv.ver_minor_ro
			WHERE		s.srv_is_active = 1
			) tServ


--- Create the Groups (Non-MSX, Environment then SQL Version)
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	VALUES ('Not MSX Managed', '', 0, 1, 0)
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	SELECT		DISTINCT
				srv_type_ro name,
				'' description,
				0 server_type,
				(
				SELECT		server_group_id
				FROM		msdb.dbo.sysmanagement_shared_server_groups_internal
				WHERE		name = 'Not MSX Managed'
							AND parent_id = 1
				) parent_id,
				0 is_system_object
	FROM		dbo.tblServer s
	LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
				AND s.srv_version_minor_ro = sv.ver_minor_ro
	WHERE		s.srv_is_active = 1
				AND s.srv_has_master_jobs = 0
INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal
	(name, description, server_type, parent_id, is_system_object)
	SELECT 		DISTINCT
				ISNULL(sv.ver_description, 'Unknown') name,
				'' description,
				0 server_type,
				(
				SELECT		t2.server_group_id
				FROM		msdb.dbo.sysmanagement_shared_server_groups_internal t1
				JOIN		msdb.dbo.sysmanagement_shared_server_groups_internal t2
								ON t1.server_group_id = t2.parent_id
				WHERE		t1.name = 'Not MSX Managed'
							AND t1.parent_id = 1
							AND t2.name = s.srv_type_ro
				) parent_id,
				0 is_system_object
	FROM		dbo.tblServer s
	LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
				AND s.srv_version_minor_ro = sv.ver_minor_ro
	WHERE		s.srv_is_active = 1
				AND s.srv_has_master_jobs = 0

--- Insert the Servers into the Groups (MSX Managed then SQL Version)
INSERT INTO [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
	(server_group_id, name, server_name, description, server_type)
	SELECT	(
			SELECT		t3.server_group_id
			FROM		msdb.dbo.sysmanagement_shared_server_groups_internal t1
			JOIN		msdb.dbo.sysmanagement_shared_server_groups_internal t2
							ON t1.server_group_id = t2.parent_id
			JOIN		msdb.dbo.sysmanagement_shared_server_groups_internal t3
							ON t2.server_group_id = t3.parent_id
			WHERE		t1.name = 'Not MSX Managed'
						AND t1.parent_id = 1
						AND t2.name = tServ.srv_type_ro
						AND t3.name = tServ.SQLServer_description
			) server_group_id, 
			srv_server_name name, 
			srv_server_name server_name, 
			ISNULL(srv_description, '') description,
			0 server_type
	FROM	(
			SELECT 		s.srv_type_ro,
						ISNULL(sv.ver_description, 'Unknown') SQLServer_description,
						s.srv_server_name,
						s.srv_description
			FROM		dbo.tblServer s
			LEFT JOIN	dbo.tblSQLVersion sv ON s.srv_version_major_ro = sv.ver_major_ro
						AND s.srv_version_minor_ro = sv.ver_minor_ro
			WHERE		s.srv_is_active = 1
						AND s.srv_has_master_jobs = 0
			) tServ
			
SET NOCOUNT OFF